﻿using NPOI.HPSF;
using NPOI.HSSF.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using System.Reflection;
using System.Text;

namespace GoodAdmin.System;

public class ExcelExportHelper<T>
{
    /// <summary>
    /// Excel导出.
    /// </summary>
    /// <param name="list">数据源.</param>
    /// <param name="excelConfig">导出设置包含文件名、标题、列设置.</param>
    /// <param name="fileName">导出文件名称.</param>
    public static FileStreamResult Export(List<T> list, ExcelConfig excelConfig, string fileName)
    {
        MemoryStream stream = ExportXlsxMemoryStream(list, excelConfig);
        return ExportWrite(stream,fileName);
    }

    /// <summary>
    /// Excel导出.
    /// </summary>
    /// <param name="list">数据源.</param>
    /// <param name="excelConfig">导出设置包含文件名、标题、列设置.</param>
    /// <param name="fileName">导出文件名称.</param>
    /// <param name="columnList">列名称.</param>
    public static FileStreamResult Export(List<Dictionary<string, object>> list, ExcelConfig excelConfig, string fileName, List<string> columnList)
    {
        MemoryStream stream = ExportMemoryStream(list, excelConfig, columnList);
        return ExportWrite(stream, fileName);
    }

    /// <summary>
    /// Excel导出.
    /// </summary>
    /// <param name="list">数据源.</param>
    /// <param name="excelConfig">导出设置包含文件名、标题、列设置.</param>
    public static MemoryStream ToStream(List<T> list, ExcelConfig excelConfig)
    {
        return ExportMemoryStream(list, excelConfig);
    }

    /// <summary>
    /// Excel导出.
    /// </summary>
    /// <param name="list">数据源.</param>
    /// <param name="path">模板路径.</param>
    /// <param name="fileName">导出文件名称.</param>
    public static FileStreamResult Export(List<ExcelTemplateModel> list, string path, string fileName)
    {
        try
        {
            MemoryStream stream = ExcelExportTemplate(list, path);
            return ExportWrite(stream, fileName);
        }
        catch (Exception)
        {
            throw;
        }
    }

    /// <summary>
    /// 导出文件.
    /// </summary>
    /// <param name="filePath">文件保存路径.</param>
    /// <param name="memoryStream">文件流.</param>
    public static FileStreamResult ExportWrite(string filePath, MemoryStream memoryStream)
    {
        try
        {
            // 获取文件夹地址
            string directoryPath = Path.GetDirectoryName(filePath);
            // 如果文件夹不存在，则创建
            if (!Directory.Exists(directoryPath))
            {
                Directory.CreateDirectory(directoryPath);
            }
            var result= new FileStreamResult(memoryStream, "application/octet-stream")
            {
                FileDownloadName = Path.GetFileName(filePath),
            };
            //手动关闭流
            if (memoryStream.CanRead)
            {
                memoryStream.Close();
            }
            return result;
        }
        catch (Exception)
        {
            throw;
        }
    }

    /// <summary>
    /// 导出文件.
    /// </summary>
    /// <param name="memoryStream">文件流</param>
    /// <param name="fileName">导出名称</param>
    /// <returns></returns>
    public static FileStreamResult ExportWrite(MemoryStream memoryStream, string fileName)
    {
        try
        {
            if (string.IsNullOrWhiteSpace(fileName))
            {
                fileName= DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx";
            }
            var result = new FileStreamResult(memoryStream, "application/octet-stream")
            {
                FileDownloadName = fileName
            };
            //手动关闭流
            if (memoryStream.CanRead)
            {
                memoryStream.Close();
            }
            return result;
        }
        catch (Exception)
        {
            throw;
        }
    }
    #region List<T>导出到Excel模板

    /// <summary>
    /// 数据导出到Excel模板.
    /// </summary>
    /// <param name="list">数据源.</param>
    /// <param name="filePath">模板路径.</param>
    public static MemoryStream ExcelExportTemplate(List<ExcelTemplateModel> list, string filePath)
    {
        try
        {
            FileStream fileStream = new(filePath, FileMode.Open, FileAccess.Read);
            ISheet? sheet = null;

            // 2003
            if (filePath.EndsWith(".xls"))
            {
                HSSFWorkbook hssfworkbook = new(fileStream);
                sheet = hssfworkbook.GetSheetAt(0);
                SetPurchaseOrder(sheet, list);
                sheet.ForceFormulaRecalculation = true;
                MemoryStream ms = new();
                hssfworkbook.Write(ms);
                ms.Flush();
                return ms;
            }

            // 2007
            else
            {
                XSSFWorkbook xssfworkbook = new(fileStream);
                sheet = xssfworkbook.GetSheetAt(0);
                SetPurchaseOrder(sheet, list);
                sheet.ForceFormulaRecalculation = true;
                MemoryStream ms = new();
                xssfworkbook.Write(ms);
                ms.Flush();
                return ms;
            }

        }
        catch (Exception)
        {
            throw;
        }
    }

    /// <summary>
    /// 赋值单元格.
    /// </summary>
    /// <param name="sheet"></param>
    /// <param name="list"></param>
    private static void SetPurchaseOrder(ISheet sheet, List<ExcelTemplateModel> list)
    {
        try
        {
            foreach (ExcelTemplateModel item in list)
            {
                IRow? row = null;
                ICell? cell = null;
                row = sheet.GetRow(item.Row);
                row ??= sheet.CreateRow(item.Row);
                cell = row.GetCell(item.Cell);
                cell ??= row.CreateCell(item.Cell);
                cell.SetCellValue(item.Value);
            }
        }
        catch (Exception)
        {
            throw;
        }
    }

    /// <summary>
    /// 合并单元格.
    /// </summary>
    /// <param name="sheet">要合并单元格所在的sheet.</param>
    /// <param name="rowstart">开始行的索引.</param>
    /// <param name="rowend">结束行的索引.</param>
    /// <param name="colstart">开始列的索引.</param>
    /// <param name="colend">结束列的索引.</param>
    public static void SetCellRangeAddress(ISheet sheet, int rowstart, int rowend, int colstart, int colend)
    {
        CellRangeAddress cellRangeAddress = new(rowstart, rowend, colstart, colend);
        sheet.AddMergedRegion(cellRangeAddress);
    }

    #endregion

    #region  List<T>导出到Excel的MemoryStream

    /// <summary>
    /// 导出到Excel的MemoryStream Export().
    /// </summary>
    /// <param name="list">数据源.</param>
    /// <param name="excelConfig">导出设置包含文件名、标题、列设置.</param>
    public static MemoryStream ExportMemoryStream(List<T> list, ExcelConfig excelConfig)
    {
        try
        {
            HSSFWorkbook workbook = new();
            ISheet sheet = workbook.CreateSheet();
            Type type = typeof(T);
            PropertyInfo[] properties = type.GetProperties();

            #region 右击文件 属性信息

            DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            dsi.Company = "NPOI";
            workbook.DocumentSummaryInformation = dsi;
            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();

            // 填加xls文件作者信息
            si.Author = "zhenghao";

            // 填加xls文件创建程序信息
            si.ApplicationName = "GoodAdmin";

            // 填加xls文件最后保存者信息
            si.LastAuthor = "zhenghao";

            // 填加xls文件作者信息
            si.Comments = "GoodAdmin";

            // 填加xls文件标题信息
            si.Title = excelConfig.FileName;

            // 填加文件主题信息
            si.Subject = excelConfig.FileName;
            si.CreateDateTime = DateTime.Now;
            workbook.SummaryInformation = si;

            #endregion

            #region 设置标题样式

            ICellStyle headStyle = workbook.CreateCellStyle();
            int[] arrColWidth = new int[properties.Length];

            // 列名
            string[] arrColName = new string[properties.Length];

            // 样式表
            // headStyle.BorderBottom = BorderStyle.Thin;
            // headStyle.BorderLeft = BorderStyle.Thin;
            // headStyle.BorderRight = BorderStyle.Thin;
            // headStyle.BorderTop = BorderStyle.Thin;
            ICellStyle[] arrayColumnStyle = new ICellStyle[properties.Length];
            headStyle.Alignment = HorizontalAlignment.Left;
            if (excelConfig.Background != null)
            {                
                headStyle.FillPattern = FillPattern.SolidForeground;
                headStyle.FillForegroundColor = GetXLColour(workbook, excelConfig.Background);
            }

            IFont font = workbook.CreateFont();
            font.FontHeightInPoints = excelConfig.TitlePoint;
            if (excelConfig.ForeColor != null)
                font.Color = GetXLColour(workbook, excelConfig.ForeColor);
            font.IsBold = true;
            headStyle.SetFont(font);

            #endregion

            #region 列头及样式

            ICellStyle cHeadStyle = workbook.CreateCellStyle();
            cHeadStyle.Alignment = HorizontalAlignment.Left;
            IFont cfont = workbook.CreateFont();
            cfont.FontHeightInPoints = excelConfig.HeadPoint;
            cHeadStyle.SetFont(cfont);

            #endregion

            #region 设置内容单元格样式

            int i = 0;
            foreach (PropertyInfo column in properties)
            {
                ICellStyle columnStyle = workbook.CreateCellStyle();
                columnStyle.Alignment = HorizontalAlignment.Left;
                arrColWidth[i] = Encoding.GetEncoding(0).GetBytes(column.Name).Length;
                arrColName[i] = column.Name;

                ExcelColumnModel columnModel = excelConfig.ColumnModel.Find(t => t.Column == column.Name);
                if (columnModel != null)
                {
                    arrColName[i] = columnModel.ExcelColumn;
                    if (columnModel.Width != 0)
                        arrColWidth[i] = columnModel.Width;

                    if (columnModel.Background != null)
                    {
                        columnStyle.FillPattern = FillPattern.SolidForeground;
                        columnStyle.FillForegroundColor = GetXLColour(workbook, columnModel.Background);
                    }

                    if (columnModel.Font != null || columnModel.Point != 0 || columnModel.ForeColor != null)
                    {
                        IFont columnFont = workbook.CreateFont();
                        columnFont.FontHeightInPoints = 10;
                        if (columnModel.Font != null)
                            columnFont.FontName = columnModel.Font;
                        if (columnModel.Point != 0)
                            columnFont.FontHeightInPoints = columnModel.Point;
                        if (columnModel.ForeColor != null)
                            columnFont.Color = GetXLColour(workbook, columnModel.ForeColor);
                        columnStyle.SetFont(font);
                    }

                    arrayColumnStyle[i] = columnStyle;
                    i++;
                }
            }

            #endregion

            #region 填充数据

            ICellStyle dataStyle = workbook.CreateCellStyle();
            IDataFormat format = workbook.CreateDataFormat();
            dataStyle.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm:ss");
            int rowIndex = 0;

            #region 新建表，填充表头，填充列头，样式

            // if (rowIndex == 65535 || rowIndex == 0)
            // {
            // if (rowIndex != 0)
            // {
            //    sheet = workbook.CreateSheet();
            // }

            #region 表头及样式
            {
                if (excelConfig.Title != null)
                {
                    IRow headerRow = sheet.CreateRow(0);
                    if (excelConfig.TitleHeight != 0)
                    {
                        headerRow.Height = (short)(excelConfig.TitleHeight * 20);
                    }

                    headerRow.HeightInPoints = 25;
                    headerRow.CreateCell(0).SetCellValue(excelConfig.Title);
                    headerRow.GetCell(0).CellStyle = headStyle;
                    sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, excelConfig.ColumnModel.Count - 1)); // ------------------
                }
            }
            #endregion

            #region 列头及样式
            {
                int rownum = 0;
                if (excelConfig.Title != null)
                {
                    rowIndex = 2;
                    rownum = 1;
                }
                else
                {
                    rowIndex = 1;
                }

                IRow headerRow = sheet.CreateRow(rownum);

                #region 如果设置了列标题就按列标题定义列头，没定义直接按字段名输出

                int headIndex = 0;
                foreach (ExcelColumnModel excelColumnModel in excelConfig.ColumnModel)
                {
                    headerRow.CreateCell(headIndex).SetCellValue(excelColumnModel.ExcelColumn);
                    headerRow.GetCell(headIndex).CellStyle = cHeadStyle;

                    // 设置列宽
                    sheet.SetColumnWidth(headIndex, (arrColWidth[headIndex] + 1) * 256);
                    headIndex++;
                }
                #endregion
            }
            #endregion

            #endregion

            if (list != null)
            {
                foreach (T item in list)
                {
                    #region 填充内容

                    if ((rowIndex % 65536) == 0)
                    {
                        if (rowIndex != 0)
                        {
                            IRow headerRow = sheet.GetRow(0);
                            sheet = workbook.CreateSheet();
                            IRow dataRow_headerRow = sheet.CreateRow(0);
                            int k = 0;
                            foreach (ICell cell in headerRow.Cells)
                            {
                                ICell newCell = dataRow_headerRow.CreateCell(k);
                                newCell.CellStyle = arrayColumnStyle[k];
                                string drValue = cell.StringCellValue;
                                SetCell(newCell, dataStyle, drValue.GetType(), drValue);
                                k++;
                            }

                            rowIndex = 1;
                        }
                    }

                    IRow dataRow = sheet.CreateRow(rowIndex);
                    int ordinal = 0;
                    foreach (ExcelColumnModel column in excelConfig.ColumnModel)
                    {
                        PropertyInfo? columnValue = properties.Where(it => it.Name == column.Column).FirstOrDefault();
                        if (columnValue != null)
                        {
                            ICell newCell = dataRow.CreateCell(ordinal);
                            newCell.CellStyle = arrayColumnStyle[ordinal];
                            string? drValue = columnValue.GetValue(item, null) == null ? string.Empty : columnValue.GetValue(item, null).ToString();
                            SetCell(newCell, dataStyle, columnValue.PropertyType, drValue);
                            ordinal++;
                        }
                    }

                    #endregion

                    rowIndex++;
                }
            }

            #endregion

            MemoryStream ms = new();
            workbook.Write(ms);
            ms.Position = 0;
            return ms;
        }
        catch (Exception)
        {
            throw;
        }
    }

    /// <summary>
    /// xlsx导出到内存流
    /// </summary>
    /// <param name="list"></param>
    /// <param name="excelConfig"></param>
    /// <returns></returns>
    public static MemoryStream ExportXlsxMemoryStream(List<T> list, ExcelConfig excelConfig)
    {
        try
        {
            XSSFWorkbook workbook = new();
            ISheet sheet = workbook.CreateSheet();

            #region 右击文件 属性信息    
            // 创建文档摘要信息 todo 


            #endregion

            #region 设置标题样式

            ICellStyle headStyle = (XSSFCellStyle)workbook.CreateCellStyle();


            // 样式表
            // headStyle.BorderBottom = BorderStyle.Thin;
            // headStyle.BorderLeft = BorderStyle.Thin;
            // headStyle.BorderRight = BorderStyle.Thin;
            // headStyle.BorderTop = BorderStyle.Thin;

            headStyle.Alignment = HorizontalAlignment.Center;
            headStyle.VerticalAlignment = VerticalAlignment.Center;
            if (excelConfig.Background != null)
            {
                headStyle.FillPattern = FillPattern.SolidForeground;
                headStyle.FillForegroundColor = GetXSSFColour(excelConfig.Background);
            }

            IFont font = workbook.CreateFont();
            font.FontHeightInPoints = excelConfig.TitlePoint;
            if (excelConfig.ForeColor != null)
                font.Color = GetXSSFColour(excelConfig.ForeColor);
            font.IsBold = true;
            headStyle.SetFont(font);

            #endregion

            #region 设置列头及内容单元格样式
            Type type = typeof(T);
            PropertyInfo[] properties = type.GetProperties();

            int[] arrColWidth = new int[properties.Length];

            // 列名
            string[] arrColName = new string[properties.Length];
            ICellStyle[] arrayColumnStyle = new ICellStyle[properties.Length];//内容样式
            ICellStyle[] arrayColumnHeaderStyle = new ICellStyle[properties.Length];//列头样式

            int i = 0;
            foreach (PropertyInfo column in properties)
            {
                #region 列头样式
                ICellStyle cHeadStyle = workbook.CreateCellStyle();
                cHeadStyle.Alignment = HorizontalAlignment.Left;
                IFont cfont = workbook.CreateFont();
                cfont.FontHeightInPoints = excelConfig.HeadPoint;
                cHeadStyle.SetFont(cfont);
                  ExcelColumnHeaderModel columnHeaderModel = excelConfig.ColumnHeaderModel.Find(t => t.Column == column.Name);
                if(columnHeaderModel != null)
                {
                    arrColName[i] = columnHeaderModel.ExcelColumn;

                    cHeadStyle.BorderBottom = BorderStyle.Thin;
                    cHeadStyle.BorderLeft = BorderStyle.Thin;
                    cHeadStyle.BorderRight = BorderStyle.Thin;
                    cHeadStyle.BorderTop = BorderStyle.Thin;

                    if (columnHeaderModel.Width != 0)
                        arrColWidth[i] = columnHeaderModel.Width;
                    if (columnHeaderModel.Background != null)
                    {
                        cHeadStyle.FillPattern = FillPattern.SolidForeground;
                        cHeadStyle.FillBackgroundColor = GetXSSFColour(columnHeaderModel.Background);
                    }
                    
                    //列头字体   
                    IFont columnFont = workbook.CreateFont();
                    if (columnHeaderModel.Font != null)
                        columnFont.FontName = columnHeaderModel.Font;
                    if (columnHeaderModel.Point != 0)
                        columnFont.FontHeightInPoints = columnHeaderModel.Point;
                    if (columnHeaderModel.ForeColor != null)
                        columnFont.Color = GetXSSFColour(columnHeaderModel.ForeColor);
                    columnFont.FontHeightInPoints= columnHeaderModel.Point==0 ? columnHeaderModel.Point : excelConfig.HeadPoint;
                    cHeadStyle.SetFont(columnFont);
                                     
                }
                arrayColumnHeaderStyle[i] = cHeadStyle;
                #endregion 列头样式


                #region 内容样式
                ICellStyle columnStyle = workbook.CreateCellStyle();
                columnStyle.Alignment = HorizontalAlignment.Left;
                arrColWidth[i] = Encoding.GetEncoding(0).GetBytes(columnHeaderModel.ExcelColumn).Length;
                arrColName[i] = column.Name;

                ExcelColumnModel columnModel = excelConfig.ColumnModel.Find(t => t.Column == column.Name);
                if (columnModel != null)
                {
                    arrColName[i] = columnModel.ExcelColumn;
                    if (columnModel.Width != 0)
                        arrColWidth[i] = columnModel.Width;
                    if (columnModel.Background != null)
                    {
                        columnStyle.FillPattern = FillPattern.SolidForeground;
                        columnStyle.FillForegroundColor = GetXSSFColour(columnModel.Background);
                    }

                    IFont columnFont = workbook.CreateFont();
                    columnFont.FontHeightInPoints = 10;
                    if (columnModel.Font != null)
                        columnFont.FontName = columnModel.Font;
                    if (columnModel.Point != 0)
                        columnFont.FontHeightInPoints = columnModel.Point;
                    if (columnModel.ForeColor != null)
                        columnFont.Color = GetXSSFColour(columnModel.ForeColor);
                    columnStyle.SetFont(columnFont);
                                       
                }
                arrayColumnStyle[i] = columnStyle;
                #endregion
                i++;
            }

            #endregion

            #region 填充数据

            ICellStyle dataStyle = workbook.CreateCellStyle();
            IDataFormat format = workbook.CreateDataFormat();
            dataStyle.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm:ss");


            #region 新建表，填充表头，填充列头，样式
            int rowIndex = 0;
            #region 标题及样式
            {
                if (!string.IsNullOrWhiteSpace(excelConfig.Title))
                {
                    IRow headerRow = sheet.CreateRow(rowIndex);
                    if (excelConfig.TitleHeight != 0)
                    {
                        headerRow.Height = (short)(excelConfig.TitleHeight * 20);
                    }

                    headerRow.HeightInPoints = 25;
                    headerRow.CreateCell(0).SetCellValue(excelConfig.Title);
                    headerRow.GetCell(0).CellStyle = headStyle;
                    sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, excelConfig.ColumnModel.Count - 1));
                    rowIndex++;
                }
            }
            #endregion
            #region 填充内容  
            #region 列头及样式            
            {              
                IRow headerRow = sheet.CreateRow(rowIndex);

                #region 设置列头

                int headIndex = 0;
                foreach (ExcelColumnModel excelColumnModel in excelConfig.ColumnModel)
                {
                    headerRow.CreateCell(headIndex).SetCellValue(excelColumnModel.ExcelColumn);
                    headerRow.GetCell(headIndex).CellStyle = arrayColumnHeaderStyle[headIndex];

                    // 设置列宽
                    sheet.SetColumnWidth(headIndex, (arrColWidth[headIndex] + 4) << 8);
                    headIndex++;
                }
                rowIndex++;
                #endregion
            }
            #endregion

            #endregion 填充表体内容
            
            foreach (T item in list)
            {                                      
                IRow dataRow = sheet.CreateRow(rowIndex);
                int ordinal = 0;
                foreach (ExcelColumnModel column in excelConfig.ColumnModel)
                {
                    PropertyInfo? columnValue = properties.Where(it => it.Name == column.Column).FirstOrDefault();
                    if (columnValue != null)
                    {
                        ICell newCell = dataRow.CreateCell(ordinal);
                        newCell.CellStyle = arrayColumnStyle[ordinal];
                        string? drValue = columnValue.GetValue(item, null) == null ? string.Empty : columnValue.GetValue(item, null).ToString();
                        SetCell(newCell, dataStyle, columnValue.PropertyType, drValue);
                        ordinal++;
                    }
                }
               rowIndex++;
            }
            #endregion

            #endregion

            NPOIMemoryStream ms = new()
            {
                AllowClose = false
            };
            workbook.Write(ms);
            ms.Seek(0, SeekOrigin.Begin); // 重置位置到开头
            return ms;
        }
        catch (Exception)
        {
            throw;
        }
    }
    #endregion

    #region  
    /// <summary>
    /// 导出到Excel的MemoryStream
    /// </summary>
    /// <param name="list"></param>
    /// <param name="excelConfig"></param>
    /// <param name="columnList"></param>
    /// <returns></returns>
    public static MemoryStream ExportMemoryStream(List<Dictionary<string, object>> list, ExcelConfig excelConfig, List<string> columnList)
    {
        try
        {
            HSSFWorkbook workbook = new();
            ISheet sheet = workbook.CreateSheet();

            #region 右击文件 属性信息
            {
                DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
                dsi.Company = "NPOI";
                workbook.DocumentSummaryInformation = dsi;
                SummaryInformation si = PropertySetFactory.CreateSummaryInformation();

                // 填加xls文件作者信息
                si.Author = "zhenghao";

                // 填加xls文件创建程序信息
                si.ApplicationName = "GoodAdmin";

                // 填加xls文件最后保存者信息
                si.LastAuthor = "zhenghao";

                // 填加xls文件作者信息
                si.Comments = "GoodAdmin";

                // 填加xls文件标题信息
                si.Title = "标题信息";

                // 填加文件主题信息
                si.Subject = "主题信息";
                si.CreateDateTime = DateTime.Now;
                workbook.SummaryInformation = si;
            }
            #endregion

            #region 设置标题样式
            ICellStyle headStyle = workbook.CreateCellStyle();
            int[] arrColWidth = new int[columnList.Count];

            // 列名
            string[] arrColName = new string[columnList.Count];

            // 样式表
            // headStyle.BorderBottom = BorderStyle.Thin;
            // headStyle.BorderLeft = BorderStyle.Thin;
            // headStyle.BorderRight = BorderStyle.Thin;
            // headStyle.BorderTop = BorderStyle.Thin;
            ICellStyle[] arrayColumnStyle = new ICellStyle[columnList.Count];
            headStyle.Alignment = HorizontalAlignment.Left;
            if (excelConfig.Background != null)
            {
                if (excelConfig.Background != null)
                {
                    headStyle.FillPattern = FillPattern.SolidForeground;
                    headStyle.FillForegroundColor = GetXLColour(workbook, excelConfig.Background);
                }
            }

            IFont font = workbook.CreateFont();
            font.FontHeightInPoints = excelConfig.TitlePoint;
            if (excelConfig.ForeColor != null)
                font.Color = GetXLColour(workbook, excelConfig.ForeColor);

            font.IsBold = true;
            headStyle.SetFont(font);
            #endregion

            #region 列头及样式

            ICellStyle cHeadStyle = workbook.CreateCellStyle();
            cHeadStyle.Alignment = HorizontalAlignment.Left;
            IFont cfont = workbook.CreateFont();
            cfont.FontHeightInPoints = excelConfig.HeadPoint;
            cHeadStyle.SetFont(cfont);

            #endregion

            #region 设置内容单元格样式

            int i = 0;

            foreach (string column in columnList)
            {
                ICellStyle columnStyle = workbook.CreateCellStyle();
                columnStyle.Alignment = HorizontalAlignment.Left;
                arrColWidth[i] = Encoding.GetEncoding(0).GetBytes(column).Length;
                arrColName[i] = column;

                if (excelConfig.ColumnModel != null)
                {
                    ExcelColumnModel columnModel = excelConfig.ColumnModel.Find(t => t.ExcelColumn == column);
                    if (columnModel != null)
                    {
                        arrColName[i] = columnModel.ExcelColumn;
                        if (columnModel.Width != 0)
                            arrColWidth[i] = columnModel.Width;
                        if (columnModel.Background != null)
                        {

                                columnStyle.FillPattern = FillPattern.SolidForeground;
                                columnStyle.FillForegroundColor = GetXLColour(workbook, columnModel.Background);
                        }

                        if (columnModel.Font != null || columnModel.Point != 0 || columnModel.ForeColor != null)
                        {
                            IFont columnFont = workbook.CreateFont();
                            columnFont.FontHeightInPoints = 10;
                            if (columnModel.Font != null)
                                columnFont.FontName = columnModel.Font;
                            if (columnModel.Point != 0)
                                columnFont.FontHeightInPoints = columnModel.Point;
                            if (columnModel.ForeColor != null)
                                columnFont.Color = GetXLColour(workbook, columnModel.ForeColor);

                            columnStyle.SetFont(font);
                        }
                    }
                }

                arrayColumnStyle[i] = columnStyle;
                i++;
            }

            #endregion

            #region 填充数据

            ICellStyle dataStyle = workbook.CreateCellStyle();
            IDataFormat format = workbook.CreateDataFormat();
            dataStyle.DataFormat = format.GetFormat("yyyy-MM-dd");
            int rowIndex = 0;
            #region 新建表，填充表头，填充列头，样式

            //if (rowIndex == 65535 || rowIndex == 0)
            //{
            //    if (rowIndex != 0)
            //    {
            //        sheet = workbook.CreateSheet();
            //    }

            #region 表头及样式
            {
                if (excelConfig.Title != null)
                {
                    IRow headerRow = sheet.CreateRow(0);
                    if (excelConfig.TitleHeight != 0)
                    {
                        headerRow.Height = (short)(excelConfig.TitleHeight * 20);
                    }
                    headerRow.HeightInPoints = 25;
                    headerRow.CreateCell(0).SetCellValue(excelConfig.Title);
                    headerRow.GetCell(0).CellStyle = headStyle;
                    sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, excelConfig.ColumnModel.Count - 1)); // ------------------
                }
            }
            #endregion

            #region 列头及样式
            {
                var _rownum = 0;
                if (excelConfig.Title != null)
                {
                    rowIndex = 2;
                    _rownum = 1;
                }
                else
                {
                    rowIndex = 1;
                }

                IRow headerRow = sheet.CreateRow(_rownum);

                #region 如果设置了列标题就按列标题定义列头，没定义直接按字段名输出

                int headIndex = 0;
                foreach (ExcelColumnModel excelColumnModel in excelConfig.ColumnModel)
                {
                    headerRow.CreateCell(headIndex).SetCellValue(excelColumnModel.ExcelColumn);
                    headerRow.GetCell(headIndex).CellStyle = cHeadStyle;
                    // 设置列宽
                    sheet.SetColumnWidth(headIndex, (arrColWidth[headIndex] + 1) * 256);
                    headIndex++;
                }

                #endregion
            }

            #endregion

            //}

            #endregion

            if (list != null)
            {
                foreach (Dictionary<string, object> item in list)
                {
                    #region 填充内容

                    if ((rowIndex % 65536) == 0)
                    {
                        if (rowIndex != 0)
                        {
                            IRow headerRow = sheet.GetRow(0);
                            sheet = workbook.CreateSheet();
                            IRow dataRow_headerRow = sheet.CreateRow(0);
                            int k = 0;
                            foreach (ICell cell in headerRow.Cells)
                            {
                                ICell newCell = dataRow_headerRow.CreateCell(k);
                                newCell.CellStyle = arrayColumnStyle[k];
                                string drValue = cell.StringCellValue;
                                SetCell(newCell, dataStyle, drValue.GetType(), drValue);
                                k++;
                            }

                            rowIndex = 1;
                        }
                    }

                    IRow dataRow = sheet.CreateRow(rowIndex);
                    int ordinal = 0;
                    foreach (ExcelColumnModel column in excelConfig.ColumnModel)
                    {
                        var columnValue = item.Where(i => i.Key.Contains(column.Column)).FirstOrDefault();
                        if (columnValue.Key != null && columnValue.Value != null)
                        {
                            ICell newCell = dataRow.CreateCell(ordinal);
                            newCell.CellStyle = arrayColumnStyle[ordinal];
                            string drValue = columnValue.Value.ToString();
                            SetCell(newCell, dataStyle, drValue.GetType(), drValue);
                            ordinal++;
                        }
                        else
                        {
                            ICell newCell = dataRow.CreateCell(ordinal);
                            newCell.CellStyle = arrayColumnStyle[ordinal];
                            string drValue = string.Empty;
                            SetCell(newCell, dataStyle, drValue.GetType(), drValue);
                            ordinal++;
                        }
                    }

                    foreach (var column in item)
                    {
                        ExcelColumnModel columnModel = excelConfig.ColumnModel.Find(t => t.Column == column.Key);
                        if (columnModel != null)
                        {

                        }
                    }

                    #endregion
                    rowIndex++;
                }
            }
            #endregion

            MemoryStream ms = new();
            workbook.Write(ms);
            ms.Position = 0;
            return ms;
        }
        catch (Exception)
        {
            throw;
        }

    }

    #endregion

    #region 设置表格内容
    private static void SetCell(ICell newCell, ICellStyle dataStyle, Type dataType, string drValue)
    {
        string callDataType = dataType.GenericTypeArguments.Length == 0 ? dataType.ToString() : dataType.GenericTypeArguments.FirstOrDefault().ToString();

        switch (callDataType)
        {
            // 字符串类型
            case "System.String":
                newCell.SetCellValue(drValue);
                break;

            // 日期类型
            case "System.DateTime":
                {
                    if (DateTime.TryParse(drValue, out DateTime dateV))
                        newCell.SetCellValue(dateV);
                    else
                        newCell.SetCellValue(string.Empty);
                    // 格式化显示
                    newCell.CellStyle = dataStyle;
                }

                break;

            // 布尔型
            case "System.Boolean":
                bool boolV;
                _ = bool.TryParse(drValue, out boolV);
                newCell.SetCellValue(boolV);
                break;

            // 整型
            case "System.Int16":
            case "System.Int32":
            case "System.Int64":
            case "System.Byte":
                int intV;
                _ = int.TryParse(drValue, out intV);
                newCell.SetCellValue(intV);
                break;

            // 浮点型
            case "System.Decimal":
            case "System.Double":
                double doubV;
                _ = double.TryParse(drValue, out doubV);
                newCell.SetCellValue(doubV);
                break;

            // 空值处理
            case "System.DBNull":
                newCell.SetCellValue(string.Empty);
                break;
            default:
                newCell.SetCellValue(drValue);
                break;
        }
    }
    #endregion

    #region RGB颜色转NPOI颜色

    /// <summary>
    /// RGB颜色转NPOI颜色.
    /// </summary>
    /// <param name="workbook"></param>
    /// <param name="SystemColour"></param>
    /// <returns></returns>
    private static short GetXLColour(HSSFWorkbook workbook, IndexedColors SystemColour)
    {
        HSSFPalette XlPalette = workbook.GetCustomPalette();
        byte[] hexValue = SystemColour.RGB; // 获取 RGB 的 16 进制字符       

        byte r = hexValue[0]; // 提取 R 分量
        byte g = hexValue[1]; // 提取 G 分量
        byte b = hexValue[2]; // 提取 B 分量
        NPOI.HSSF.Util.HSSFColor XlColour = XlPalette.FindColor(r, g, b);
        short s;
        if (XlColour == null)
        {
            if (NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE < 255)
            {
                XlColour = XlPalette.FindSimilarColor(r, g, b);
                s = XlColour.Indexed;
            }
        }
        else
            s = XlColour.Indexed;
        return s;
    }

    private static short GetXSSFColour(IndexedColors SystemColour)
    {
        XSSFColor xSSFColor = new (SystemColour);
        return xSSFColor.Index;        
    }

    #endregion

    #region 类型转换

    private static object ValueType(Type t, string value)
    {
        string strt = "String";
        if (t.Name == "Nullable`1")
            strt = t.GetGenericArguments().FirstOrDefault().Name;
        object o = strt switch
        {
            "Decimal" => decimal.Parse(value),
            "Int" => int.Parse(value),
            "Float" => float.Parse(value),
            "DateTime" => DateTime.Parse(value),
            _ => value,
        };
        return o;
    }

    #endregion
}